package data

import (
	"context"
	"database/sql"
	"errors"
	"time"

	models "wardrobe-server/models"

	_ "github.com/marcboeker/go-duckdb"
)

var db *sql.DB

// Init opens (and creates if needed) a DuckDB database file and ensures schema.
func Init(databasePath string) error {
	var err error
	db, err = sql.Open("duckdb", databasePath)
	if err != nil {
		return err
	}
	if err = db.Ping(); err != nil {
		return err
	}
	// Create tables if they don't exist.
	_, err = db.Exec(`
CREATE TABLE IF NOT EXISTS wardrobes (
    id INTEGER,
    code VARCHAR,
    name VARCHAR,
    status VARCHAR,
    location VARCHAR,
    specs VARCHAR,
    buy_price DOUBLE,
    sale_price DOUBLE,
    description VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);
CREATE TABLE IF NOT EXISTS media (
    id INTEGER,
    wardrobe_id INTEGER,
    url VARCHAR,
    media_type VARCHAR,
    stage VARCHAR,
    description VARCHAR,
    created_at TIMESTAMP
);
CREATE TABLE IF NOT EXISTS expenses (
    id INTEGER,
    wardrobe_id INTEGER,
    stage VARCHAR,
    category VARCHAR,
    amount DOUBLE,
    note VARCHAR,
    created_at TIMESTAMP
);
CREATE TABLE IF NOT EXISTS installation (
    wardrobe_id INTEGER,
    order_time TIMESTAMP,
    customer_name VARCHAR,
    customer_phone VARCHAR,
    address VARCHAR,
    has_elevator BOOLEAN,
    install_time TIMESTAMP,
    notes VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);
`)
	return err
}

// InsertWardrobe persists a wardrobe and returns the stored record (with id/created_at ensured).
func InsertWardrobe(ctx context.Context, w models.Wardrobe) (models.Wardrobe, error) {
	if db == nil {
		return models.Wardrobe{}, errors.New("database not initialized")
	}

	// Ensure ID
	if w.ID == 0 {
		var nextID int
		err := db.QueryRowContext(ctx, "SELECT COALESCE(MAX(id), 0) + 1 FROM wardrobes").Scan(&nextID)
		if err != nil {
			return models.Wardrobe{}, err
		}
		w.ID = nextID
	}
	// Ensure timestamps
	now := time.Now()
	if w.CreatedAt.IsZero() {
		w.CreatedAt = now
	}
	if w.UpdatedAt.IsZero() {
		w.UpdatedAt = now
	}

	_, err := db.ExecContext(
		ctx,
		`INSERT INTO wardrobes (id, code, name, status, location, specs, buy_price, sale_price, description, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		w.ID, w.Code, w.Name, w.Status, w.Location, w.Specs, w.BuyPrice, w.SalePrice, w.Description, w.CreatedAt, w.UpdatedAt,
	)
	if err != nil {
		return models.Wardrobe{}, err
	}
	return w, nil
}

// ListWardrobes returns all wardrobes.
func ListWardrobes(ctx context.Context) ([]models.Wardrobe, error) {
	if db == nil {
		return nil, errors.New("database not initialized")
	}
	rows, err := db.QueryContext(ctx, `SELECT id, code, name, status, location, specs, buy_price, sale_price, description, created_at, updated_at FROM wardrobes ORDER BY id`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var result []models.Wardrobe
	for rows.Next() {
		var (
			id          int
			code        string
			name        string
			status      string
			location    string
			specs       string
			buyPrice    float64
			salePrice   float64
			description string
			createdAt   time.Time
			updatedAt   time.Time
		)
		if err := rows.Scan(&id, &code, &name, &status, &location, &specs, &buyPrice, &salePrice, &description, &createdAt, &updatedAt); err != nil {
			return nil, err
		}
		result = append(result, models.Wardrobe{
			ID:          id,
			Code:        code,
			Name:        name,
			Status:      status,
			Location:    location,
			Specs:       specs,
			BuyPrice:    buyPrice,
			SalePrice:   salePrice,
			Description: description,
			CreatedAt:   createdAt,
			UpdatedAt:   updatedAt,
		})
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return result, nil
}

// Helper: insert media batch
func InsertMediaBatch(ctx context.Context, wardrobeID int, stage string, mediaType string, urls []string) error {
	if db == nil {
		return errors.New("database not initialized")
	}
	now := time.Now()
	for _, u := range urls {
		var nextID int
		if err := db.QueryRowContext(ctx, "SELECT COALESCE(MAX(id),0)+1 FROM media").Scan(&nextID); err != nil {
			return err
		}
		if _, err := db.ExecContext(ctx, `INSERT INTO media (id, wardrobe_id, url, media_type, stage, description, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)`, nextID, wardrobeID, u, mediaType, stage, "", now); err != nil {
			return err
		}
	}
	return nil
}

// Helper: insert expense
func InsertExpense(ctx context.Context, e models.Expense) error {
	if db == nil {
		return errors.New("database not initialized")
	}
	if e.CreatedAt.IsZero() {
		e.CreatedAt = time.Now()
	}
	var nextID int
	if err := db.QueryRowContext(ctx, "SELECT COALESCE(MAX(id),0)+1 FROM expenses").Scan(&nextID); err != nil {
		return err
	}
	_, err := db.ExecContext(ctx, `INSERT INTO expenses (id, wardrobe_id, stage, category, amount, note, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)`, nextID, e.WardrobeID, e.Stage, e.Category, e.Amount, e.Note, e.CreatedAt)
	return err
}

// Upsert installation
func UpsertInstallation(ctx context.Context, info models.InstallationInfo) error {
	if db == nil {
		return errors.New("database not initialized")
	}
	now := time.Now()
	if info.CreatedAt.IsZero() {
		info.CreatedAt = now
	}
	info.UpdatedAt = now
	res, err := db.ExecContext(ctx, `UPDATE installation SET order_time=?, customer_name=?, customer_phone=?, address=?, has_elevator=?, install_time=?, notes=?, updated_at=? WHERE wardrobe_id=?`, info.OrderTime, info.CustomerName, info.CustomerPhone, info.Address, info.HasElevator, info.InstallTime, info.Notes, info.UpdatedAt, info.WardrobeID)
	if err != nil {
		return err
	}
	affected, _ := res.RowsAffected()
	if affected == 0 {
		_, err = db.ExecContext(ctx, `INSERT INTO installation (wardrobe_id, order_time, customer_name, customer_phone, address, has_elevator, install_time, notes, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`, info.WardrobeID, info.OrderTime, info.CustomerName, info.CustomerPhone, info.Address, info.HasElevator, info.InstallTime, info.Notes, info.CreatedAt, info.UpdatedAt)
	}
	return err
}

// Detailed view
func GetDetailedWardrobe(ctx context.Context, id int) (models.DetailedWardrobe, error) {
	if db == nil {
		return models.DetailedWardrobe{}, errors.New("database not initialized")
	}
	var w models.Wardrobe
	row := db.QueryRowContext(ctx, `SELECT id, code, name, status, location, specs, buy_price, sale_price, description, created_at, updated_at FROM wardrobes WHERE id=?`, id)
	if err := row.Scan(&w.ID, &w.Code, &w.Name, &w.Status, &w.Location, &w.Specs, &w.BuyPrice, &w.SalePrice, &w.Description, &w.CreatedAt, &w.UpdatedAt); err != nil {
		return models.DetailedWardrobe{}, err
	}
	// media
	mrows, err := db.QueryContext(ctx, `SELECT id, wardrobe_id, url, media_type, stage, description, created_at FROM media WHERE wardrobe_id=? ORDER BY id`, id)
	if err != nil {
		return models.DetailedWardrobe{}, err
	}
	defer mrows.Close()
	var mediaList []models.Media
	for mrows.Next() {
		var m models.Media
		if err := mrows.Scan(&m.ID, &m.WardrobeID, &m.URL, &m.MediaType, &m.Stage, &m.Description, &m.CreatedAt); err != nil {
			return models.DetailedWardrobe{}, err
		}
		mediaList = append(mediaList, m)
	}
	// expenses
	erows, err := db.QueryContext(ctx, `SELECT id, wardrobe_id, stage, category, amount, note, created_at FROM expenses WHERE wardrobe_id=? ORDER BY id`, id)
	if err != nil {
		return models.DetailedWardrobe{}, err
	}
	defer erows.Close()
	var expenseList []models.Expense
	for erows.Next() {
		var e models.Expense
		if err := erows.Scan(&e.ID, &e.WardrobeID, &e.Stage, &e.Category, &e.Amount, &e.Note, &e.CreatedAt); err != nil {
			return models.DetailedWardrobe{}, err
		}
		expenseList = append(expenseList, e)
	}
	// installation
	var install *models.InstallationInfo
	irow := db.QueryRowContext(ctx, `SELECT wardrobe_id, order_time, customer_name, customer_phone, address, has_elevator, install_time, notes, created_at, updated_at FROM installation WHERE wardrobe_id=?`, id)
	var tmp models.InstallationInfo
	if err := irow.Scan(&tmp.WardrobeID, &tmp.OrderTime, &tmp.CustomerName, &tmp.CustomerPhone, &tmp.Address, &tmp.HasElevator, &tmp.InstallTime, &tmp.Notes, &tmp.CreatedAt, &tmp.UpdatedAt); err == nil {
		install = &tmp
	}
	return models.DetailedWardrobe{Wardrobe: w, Media: mediaList, Expenses: expenseList, Installation: install}, nil
}

// UpdateListing sets sale info and moves status to pending_listing
func UpdateListing(ctx context.Context, id int, salePrice float64, description string) error {
	if db == nil {
		return errors.New("database not initialized")
	}
	now := time.Now()
	_, err := db.ExecContext(ctx, `UPDATE wardrobes SET status=?, sale_price=?, description=?, updated_at=? WHERE id=?`, "pending_listing", salePrice, description, now, id)
	return err
}

// SetStatus updates the wardrobe status
func SetStatus(ctx context.Context, id int, status string) error {
	if db == nil {
		return errors.New("database not initialized")
	}
	now := time.Now()
	_, err := db.ExecContext(ctx, `UPDATE wardrobes SET status=?, updated_at=? WHERE id=?`, status, now, id)
	return err
}
